﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OleDb;

namespace DataBaseBuilder
{
    public static class DataAccessLayer
    {

        private enum ColumnsGetBookingsByRange
        {
            UniqueId,
            CheckIn,
            CheckOut,
            RoomUID,
            RoomName,
            RoomColorName,
            CustomerUID,
            CustomerName,
            OriginUID,
            OriginName
        }

        public List<BookingDTO> GetBookingsByRange(DateTime from, DateTime to)
        {
            var bookings = new List<BookingDTO>();
            using (IDbConnection connection = GetConnection())
            {
                string commandString = "select TOP 1 RTRIM(LicenceData) as LicenceData from Settings";
                using (IDbCommand command = connection.CreateCommand())
                {
                    command.CommandText = commandString;
                    command.CommandType = CommandType.Text;
                    connection.Open();
                    using (IDataReader reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            bookings.Add(new BookingDTO()
                            {
                                UniqueId = reader.GetInt32((int)ColumnsGetBookingsByRange.UniqueId),
                                CheckIn = reader.GetDateTime((int)ColumnsGetBookingsByRange.CheckIn),
                                CheckOut = reader.GetDateTime((int)ColumnsGetBookingsByRange.CheckOut),
                                CustomerUID = reader.GetInt32((int)ColumnsGetBookingsByRange.CustomerUID),
                                CustomerName = reader.GetString((int)ColumnsGetBookingsByRange.CustomerName),
                                OriginUID = reader.GetValue((int)ColumnsGetBookingsByRange.OriginUID) == DBNull.Value ? (int?)null : reader.GetInt32((int)ColumnsGetBookingsByRange.OriginUID),
                                OriginName = reader.GetValue((int)ColumnsGetBookingsByRange.OriginName) == DBNull.Value ? null : reader.GetString((int)ColumnsGetBookingsByRange.OriginName),
                                RoomUID = reader.GetValue((int)ColumnsGetBookingsByRange.RoomUID) == DBNull.Value ? (int?)null : reader.GetInt32((int)ColumnsGetBookingsByRange.RoomUID),
                                RoomColorName = reader.GetValue((int)ColumnsGetBookingsByRange.RoomColorName) == DBNull.Value ? null : reader.GetString((int)ColumnsGetBookingsByRange.RoomColorName),
                            });
                        }
                    }
                    
                }
            }
            return bookings;
        }

        private static IDbConnection GetConnection()
        {
            return new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Jet OLEDB:Database Password=crusade;Data Source= + RegistryClass.GetABMControlDataBasePath()");
        }
    }
}
